﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using Microsoft.TeamFoundation.Server;
using System.Xml;
using System.Globalization;
using System.Data.SqlClient;

namespace GTA.ResourceAccess
{
    public class GTA : BaseResourceAccess
    {
        public List<BusinessEntity.Task> ListArchitectureTasks(BusinessEntity.TaskFilter filter, BusinessEntity.UserManagement.User loggedUser)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
            SELECT [System.Id], [System.AreaPath], [System.WorkItemType], [System.State], [System.AssignedTo], [System.Title], 
                   [Microsoft.VSTS.Scheduling.StartDate], [Microsoft.VSTS.Scheduling.FinishDate] FROM WorkItems 
            WHERE [System.WorkItemType] = '" + loggedUser.UserGroup.Identification + @"' ");
            sb.Append(" and [System.TeamProject] <> 'TEST' ");
            if (filter.ID != 0 && filter.ID != Int32.MinValue)
                sb.Append(@"and [System.Id] = " + filter.ID.ToString());
            if (filter.Project != null && filter.Project.Count > 0)
            {
                StringBuilder projects = new StringBuilder();
                filter.Project.ForEach(item => projects.Append("'" + item.Name + "'" + ","));
                projects.Remove(projects.Length - 1, 1);
                sb.Append(@"and [System.TeamProject] in (" + projects + ") ");
            }
            if (filter.Category != null && filter.Category.Count > 0)
            {
                StringBuilder categories = new StringBuilder();
                filter.Category.ForEach(item => categories.Append("'" + item.Description + "'" + ","));
                categories.Remove(categories.Length - 1, 1);
                sb.Append(@"and [Bovespa.Sinacor.TaskArquitetura.TA_TipoTarefa] in (" + categories + ") ");
            }
            if (filter.DesignedTo != null && filter.DesignedTo.Count > 0)
            {
                StringBuilder responsibles = new StringBuilder();
                filter.DesignedTo.ForEach(item => responsibles.Append("'" + item.Name + "'" + ","));
                responsibles.Remove(responsibles.Length - 1, 1);
                sb.Append(@"and [System.AssignedTo] in (" + responsibles + ") ");
            }
            if (filter.State != null && filter.State.Count > 0)
            {
                StringBuilder states = new StringBuilder();
                filter.State.ForEach(item => states.Append("'" + item.Description + "'" + ","));
                states.Remove(states.Length - 1, 1);
                sb.Append(@"and [System.State] in (" + states + ") ");
            }
            if (filter.ScheduleEstimatedEndFrom != DateTime.MinValue)
                sb.Append(@"and [Microsoft.VSTS.Scheduling.FinishDate] >= '" + filter.ScheduleEstimatedEndFrom.ToString("yyyy-MM-dd") + "' ");
            if (filter.ScheduleEstimatedEndTo != DateTime.MinValue)
                sb.Append(@"and [Microsoft.VSTS.Scheduling.FinishDate] <= '" + filter.ScheduleEstimatedEndTo.ToString("yyyy-MM-dd") + "' ");
            if (filter.ScheduleEstimatedStartFrom != DateTime.MinValue)
                sb.Append(@"and [Microsoft.VSTS.Scheduling.StartDate] >= '" + filter.ScheduleEstimatedStartFrom.ToString("yyyy-MM-dd") + "' ");
            if (filter.ScheduleEstimatedStartTo != DateTime.MinValue)
                sb.Append(@"and [Microsoft.VSTS.Scheduling.StartDate] <= '" + filter.ScheduleEstimatedStartTo.ToString("yyyy-MM-dd") + "' ");
            if (filter.ScheduleRealizedStartFrom != DateTime.MinValue)
                sb.Append(@"and [Bovespa.Sinacor.InicioRealizado] >= '" + filter.ScheduleRealizedStartFrom.ToString("yyyy-MM-dd") + "' ");
            if (filter.ScheduleRealizedStartTo != DateTime.MinValue)
                sb.Append(@"and [Bovespa.Sinacor.InicioRealizado] <= '" + filter.ScheduleRealizedStartTo.ToString("yyyy-MM-dd") + "' ");
            if (filter.ScheduleRealizedEndFrom != DateTime.MinValue)
                sb.Append(@"and [Bovespa.Sinacor.TerminoRealizado] >= '" + filter.ScheduleRealizedEndFrom.ToString("yyyy-MM-dd") + "' ");
            if (filter.ScheduleRealizedEndTo != DateTime.MinValue)
                sb.Append(@"and [Bovespa.Sinacor.TerminoRealizado] <= '" + filter.ScheduleRealizedEndTo.ToString("yyyy-MM-dd") + "' ");

            return ExecuteTaskSearch(sb.ToString(), loggedUser);
        }
        private List<BusinessEntity.Task> ExecuteTaskSearch(String query, BusinessEntity.UserManagement.User loggedUser)
        {
            List<BusinessEntity.Task> tasks = null;
            WorkItemCollection col = ExecuteQuery(query);
            if (col != null && col.Count > 0)
            {
                tasks = new List<BusinessEntity.Task>();
                foreach (WorkItem item in col)
                    tasks.Add(FillTaskInformation(item, loggedUser));
            }
            return tasks;
        }
        private BusinessEntity.Task FillTaskInformation(WorkItem item, BusinessEntity.UserManagement.User loggedUser)
        {
            BusinessEntity.Task newTask = new BusinessEntity.Task();
            newTask.ID = item.Id;
            newTask.Title = item.Title;
            newTask.CreatedBy = item.CreatedBy;
            newTask.CreatedDate = item.CreatedDate;
            newTask.Description = item.Description;
            newTask.History = item.History;
            newTask.Project = new BusinessEntity.Project();
            newTask.Project.Id = item.Project.Id;
            newTask.Project.Name = item.Project.Name;
            newTask.Reason = item.Reason;

            newTask.State = new BusinessEntity.TaskDomain();
            newTask.State.Description = item.State;

            //AttemptionPoint
            Field foundItem = GetCustomItemField("Microsoft.VSTS.Common.Issue", item.Fields);
            if (foundItem != null && foundItem.Value != null)
            {
                newTask.AttemptionPoint = new BusinessEntity.TaskDomain();
                newTask.AttemptionPoint.Description = (String)foundItem.Value;
            }

            //Complexity
            foundItem = GetCustomItemField("Bovespa.Sinacor.TaskArquitetura.TA_Complexidade", item.Fields);
            if (foundItem != null && foundItem.Value != null)
            {
                newTask.Complexity = new BusinessEntity.TaskDomain();
                newTask.Complexity.Description = (String)foundItem.Value;
            }

            Decimal hoursValue = 0;
            //Horas Realizadas           
            foundItem = GetCustomItemField("Bovespa.Sinacor.HorasRealizadas", item.Fields);
            if (foundItem != null && foundItem.Value != null)
            {
                if (Decimal.TryParse(foundItem.Value.ToString(), out hoursValue))
                    newTask.ScheduleRealizedHours = hoursValue;
            }

            //Horas Previstas
            foundItem = GetCustomItemField("Bovespa.Sinacor.HorasPrevistas", item.Fields);
            if (foundItem != null && foundItem.Value != null)
            {
                if (Decimal.TryParse(foundItem.Value.ToString(), out hoursValue))
                    newTask.ScheduleEstimatedHours = hoursValue;
            }

            //ScheduleStart
            foundItem = GetCustomItemField("Microsoft.VSTS.Scheduling.StartDate", item.Fields);
            if (foundItem != null && foundItem.Value != null)
                newTask.ScheduleEstimatedStart = (DateTime)foundItem.Value;

            //ScheduleEnd
            foundItem = GetCustomItemField("Microsoft.VSTS.Scheduling.FinishDate", item.Fields);
            if (foundItem != null && foundItem.Value != null)
                newTask.ScheduleEstimatedEnd = (DateTime)foundItem.Value;

            //RealizedStart
            foundItem = GetCustomItemField("Bovespa.Sinacor.InicioRealizado", item.Fields);
            if (foundItem != null && foundItem.Value != null)
                newTask.ScheduleRealizedStart = (DateTime)foundItem.Value;

            //RealizedEnd
            foundItem = GetCustomItemField("Bovespa.Sinacor.TerminoRealizado", item.Fields);
            if (foundItem != null && foundItem.Value != null)
                newTask.ScheduleRealizedEnd = (DateTime)foundItem.Value;

            //Technology
            foundItem = GetCustomItemField("Bovespa.Sinacor.Tecnologia", item.Fields);
            if (foundItem != null && foundItem.Value != null)
            {
                newTask.Technology = new BusinessEntity.TaskDomain();
                newTask.Technology.Description = (String)foundItem.Value;
            }

            //Category
            foundItem = GetCustomItemField("Bovespa.Sinacor.TaskArquitetura.TA_TipoTarefa", item.Fields);
            if (foundItem != null && foundItem.Value != null)
            {
                newTask.Category = new BusinessEntity.TaskDomain();
                newTask.Category.Description = (String)foundItem.Value;
            }

            //Responsavel
            //System.AssignedTo
            foundItem = GetCustomItemField("System.AssignedTo", item.Fields);
            if (foundItem != null && foundItem.Value != null)
            {
                newTask.DesignedTo = new BusinessEntity.UserManagement.User();
                newTask.DesignedTo.Name = (String)foundItem.Value;

                List<BusinessEntity.UserManagement.User> users = ListUsers(new global::GTA.BusinessEntity.UserManagement.UserFilter()
                {
                    Name = newTask.DesignedTo.Name
                });
                if (users != null && users.Count == 1)
                    newTask.DesignedTo = users[0];
            }

            newTask.Iteration = item.IterationPath;
            newTask.Module = item.AreaPath;

            List<BusinessEntity.TaskDomain> states = ListStates();
            BusinessEntity.TaskDomain baseState = states.Find(it => it.Description.CompareTo(newTask.State.Description) == 0);
            if (baseState != null)
                newTask.State = baseState;

            newTask.UserHistory = ListTaskHistory(loggedUser.UserProfile == BusinessEntity.UserManagement.Profiler.User ? loggedUser : null, newTask, Int32.MinValue, Int32.MinValue);

            return newTask;
        }
        private Field GetCustomItemField(String fieldName, FieldCollection fields)
        {
            Field foundItem = null;
            if (fields != null)
                foundItem = fields.Cast<Field>().ToList().Find(fieldItem => fieldItem.ReferenceName.CompareTo(fieldName) == 0);
            return foundItem;
        }
        public List<BusinessEntity.Project> ListTeamProjects()
        {
            List<BusinessEntity.Project> list = null;
            if (GetWorkItemStore() != null && GetWorkItemStore().Projects != null && GetWorkItemStore().Projects.Count > 0)
            {
                list = new List<BusinessEntity.Project>();
                foreach (Project item in GetWorkItemStore().Projects)
                {
                    if (item.HasWorkItemReadRights)
                    {
                        if (item.Name.ToLowerInvariant().CompareTo("test") != 0)
                        {
                            BusinessEntity.Project project = new BusinessEntity.Project();
                            project.Id = item.Id;
                            project.Name = item.Name;
                            list.Add(project);
                        }
                    }
                }
            }
            return list;
        }
        public List<BusinessEntity.UserManagement.User> ListTeamSystemUsers(BusinessEntity.Project project)
        {
            List<BusinessEntity.UserManagement.User> users = null;
            if (project != null)
            {
                List<BusinessEntity.Project> projects = new List<BusinessEntity.Project>();
                projects.Add(project);
                users = ListTeamSystemUsers(projects);
            }
            return users;
        }
        public List<BusinessEntity.UserManagement.User> ListTeamSystemUsers(List<BusinessEntity.Project> selectedProjects)
        {
            List<BusinessEntity.UserManagement.User> users = null;
            //if (selectedProjects != null && selectedProjects.Count > 0)
            //{
            //    // get the Group Security Service
            //    IGroupSecurityService gsService = GetGroupSecurityService();
            //    List<Identity> applicationGroups = new List<Identity>();
            //    // retrieve all application groups (by default: Contributors, Project Administrators, Build Services and Readers)
            //    // for the project this Work Item belongs to. Application groups do not contain member information.
            //    foreach (BusinessEntity.Project itemProject in selectedProjects)
            //        applicationGroups.AddRange(gsService.ListApplicationGroups(itemProject.Uri.AbsoluteUri));

            //    // for each application group
            //    if (applicationGroups.Count > 0)
            //    {
            //        users = new List<BusinessEntity.UserManagement.User>();
            //        foreach (Identity applicationGroup in applicationGroups)
            //        {
            //            // unless this is the Readers group (By default Readers do not have permissions to add/edit Work Items)
            //            if (applicationGroup.DisplayName.ToLowerInvariant().Contains("arquitetura"))
            //            {
            //                // retrieve the application group again (this time with members information)
            //                Identity[] groupContainer = gsService.ReadIdentities(SearchFactor.Sid, new string[] { applicationGroup.Sid }, QueryMembership.Expanded);
            //                // for each group (container contains only one group)
            //                if (groupContainer.Length > 0)
            //                {
            //                    foreach (Identity group in groupContainer)
            //                    {
            //                        // if this group has any members
            //                        if (group.Members != null)
            //                        {
            //                            // for each member in this group
            //                            foreach (string projectMemberSid in group.Members)
            //                            {
            //                                Identity validUser = gsService.ReadIdentity(SearchFactor.Sid, projectMemberSid, QueryMembership.None);
            //                                if (!validUser.Deleted && validUser.Type == IdentityType.WindowsUser)
            //                                {
            //                                    BusinessEntity.UserManagement.User user = new BusinessEntity.UserManagement.User();
            //                                    user.AccountName = validUser.AccountName;
            //                                    user.Deleted = validUser.Deleted;
            //                                    user.Description = validUser.Description; //validUser.DisplayName + " - " + validUser.AccountName + " (" + validUser.Domain + ")";
            //                                    user.DisplayName = validUser.DisplayName;
            //                                    user.DistinguishedName = validUser.DistinguishedName;
            //                                    user.Domain = validUser.Domain;
            //                                    user.MailAddress = validUser.MailAddress;
            //                                    user.MemberOf = validUser.MemberOf;
            //                                    user.Members = validUser.Members;
            //                                    user.SecurityGroup = validUser.SecurityGroup;
            //                                    user.Sid = validUser.Sid;
            //                                    users.Add(user);
            //                                }
            //                            }
            //                        }
            //                    }
            //                }
            //            }
            //        }
            //    }
            //}

            ////Ordena a lista de usuários obtida
            //if (users != null)
            //    users.Sort(new Comparison<BusinessEntity.UserManagement.User>(delegate(BusinessEntity.UserManagement.User user1, BusinessEntity.UserManagement.User user2)
            //    {
            //        return user1.Name.CompareTo(user2.Name);
            //    }));

            ////Limpa as duplicidades de usuários existentes em multiplos grupos.
            //if (users != null)
            //    users.ForEach(new Action<BusinessEntity.UserManagement.User>(delegate(BusinessEntity.UserManagement.User itemUser)
            //    {
            //        while (users.Count(it => it.Name.CompareTo(itemUser.Name) == 0) > 1)
            //            users.RemoveAt(users.FindIndex(fd => fd.Name.CompareTo(itemUser.Name) == 0));
            //    }));

            return users;
        }
        public List<BusinessEntity.TaskDomain> ListStates()
        {
            List<BusinessEntity.TaskDomain> list = null;
            try
            {
                sqlCommand = "PRC_TB_STATES_SE";
                reader = ExecuteReader(sqlCommand);
                if (reader != null && reader.HasRows)
                {
                    list = new List<BusinessEntity.TaskDomain>();
                    while (reader.Read())
                        list.Add(new BusinessEntity.TaskDomain()
                        {
                            Description = MinValueString(reader["TS_DESCRIPTION"]),
                            ID = MinValueInt(reader["TS_ID"])
                        });
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
        }
        public List<BusinessEntity.TaskDomain> ListStatesTeamSystem(BusinessEntity.UserManagement.User loggedUser)
        {
            List<BusinessEntity.TaskDomain> states = null;

            List<BusinessEntity.Task> tasks = this.ListArchitectureTasks(new BusinessEntity.TaskFilter(), loggedUser);
            if (tasks != null)
            {
                states = new List<BusinessEntity.TaskDomain>();

                var result = from unit in tasks
                             group unit by unit.State.Description into valores
                             select valores;

                List<BusinessEntity.Task> tasksStateNull = tasks.FindAll(it => it.State == null);

                foreach (IGrouping<String, BusinessEntity.Task> itemLookup in result.ToList())
                {
                    if (!String.IsNullOrEmpty(itemLookup.Key))
                        states.Add(new BusinessEntity.TaskDomain(0, itemLookup.Key));
                }
            }
            return states;
        }
        public List<BusinessEntity.TaskDomain> ListCategories()
        {
            List<BusinessEntity.TaskDomain> list = null;
            try
            {
                sqlCommand = "PRC_TB_CATEGORIES_SE";
                reader = ExecuteReader(sqlCommand);
                if (reader != null && reader.HasRows)
                {
                    list = new List<BusinessEntity.TaskDomain>();
                    while (reader.Read())
                        list.Add(new BusinessEntity.TaskDomain()
                        {
                            Description = MinValueString(reader["TC_DESCRIPTION"]),
                            ID = MinValueInt(reader["TC_ID"])
                        });
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
        }
        public List<BusinessEntity.TaskDomain> ListCategoriesTeamSystem(BusinessEntity.UserManagement.User loggedUser)
        {
            List<BusinessEntity.TaskDomain> categories = null;

            List<BusinessEntity.Task> tasks = this.ListArchitectureTasks(new BusinessEntity.TaskFilter(), loggedUser);
            if (tasks != null)
            {
                categories = new List<BusinessEntity.TaskDomain>();

                var result = from unit in tasks
                             group unit by unit.Category.Description into valores
                             select valores;

                foreach (IGrouping<String, BusinessEntity.Task> itemLookup in result.ToList())
                {
                    if (!String.IsNullOrEmpty(itemLookup.Key))
                        categories.Add(new BusinessEntity.TaskDomain(0, itemLookup.Key));
                }
            }

            return categories;
        }
        public List<BusinessEntity.TaskDomain> ListTaskHistoryGeneralClassifications()
        {
            List<BusinessEntity.TaskDomain> list = null;
            try
            {
                sqlCommand = "PRC_TB_USER_TASK_HISTORY_CLASSIFICATION_SE";
                reader = ExecuteReader(sqlCommand);

                if (reader != null && reader.HasRows)
                {
                    list = new List<global::GTA.BusinessEntity.TaskDomain>();
                    while (reader.Read())
                        list.Add(new BusinessEntity.TaskDomain(MinValueInt(reader["TUHC_ID"]),
                                                               MinValueString(reader["TUHC_DESCRIPTION"])));
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
        }
        public List<BusinessEntity.TaskField> ListFieldDefinitions()
        {
            List<BusinessEntity.TaskField> list = null;
            if (GetWorkItemStore() != null && GetWorkItemStore().FieldDefinitions != null && GetWorkItemStore().FieldDefinitions.Count > 0)
            {
                list = new List<BusinessEntity.TaskField>();
                foreach (FieldDefinition item in GetWorkItemStore().FieldDefinitions)
                {
                    BusinessEntity.TaskField field = new BusinessEntity.TaskField();
                    field.CanSortBy = item.CanSortBy;
                    field.FieldTypeDefinition = (BusinessEntity.TaskField.FieldType)item.FieldType;
                    field.HelpText = item.HelpText;
                    field.Id = item.Id;
                    field.IsComputed = item.IsComputed;
                    field.IsEditable = item.IsEditable;
                    field.IsQueryable = item.IsQueryable;
                    field.Name = item.Name;
                    field.ReferenceName = item.ReferenceName;
                    field.SystemType = item.SystemType;
                    list.Add(field);
                }
            }
            return list;
        }
        public List<BusinessEntity.Group> ListGroups()
        {
            List<BusinessEntity.Group> groups = null;
            try
            {
                sqlCommand = "PRC_TB_GROUPS_SE";
                reader = ExecuteReader(sqlCommand);
                if (reader != null && reader.HasRows)
                {
                    groups = new List<BusinessEntity.Group>();
                    while (reader.Read())
                        groups.Add(new BusinessEntity.Group()
                        {
                            ID = MinValueInt(reader["tg_id"]),
                            Name = MinValueString(reader["tg_name"]),
                            Identification = MinValueString(reader["tg_identification"]),
                            Description = MinValueString(reader["tg_description"])
                        });
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return groups;
        }
        public void SaveGroup(BusinessEntity.Group group)
        {
            sqlCommand = "PRC_TB_GROUPS_IN";
            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@TGDESCRIPTION", group.Description);
            parameters[1] = new SqlParameter("@TGIDENTIFICATION", group.Identification);
            parameters[2] = new SqlParameter("@TGNAME", group.Name);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void UpdateGroup(BusinessEntity.Group group)
        {
            sqlCommand = "PRC_TB_GROUPS_UP";
            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@TGID", group.ID);
            parameters[1] = new SqlParameter("@TGDESCRIPTION", group.Description);
            parameters[2] = new SqlParameter("@TGIDENTIFICATION", group.Identification);
            parameters[3] = new SqlParameter("@TGNAME", group.Name);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void DeleteGroup(int groupID)
        {
            sqlCommand = "PRC_TB_GROUPS_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TGID", groupID);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public BusinessEntity.UserManagement.User GetUser(int userID)
        {
            BusinessEntity.UserManagement.User user = null;
            try
            {
                sqlCommand = "PRC_TB_USER_SE";
                SqlParameter[] parameters = new SqlParameter[6];
                parameters[0] = new SqlParameter("@TUID", userID);
                parameters[1] = new SqlParameter("@TUNAME", (object)DBNull.Value);
                parameters[2] = new SqlParameter("@TULOGIN", (object)DBNull.Value);
                parameters[3] = new SqlParameter("@TUPASSWORD", (object)DBNull.Value);
                parameters[4] = new SqlParameter("@TUPROFILERID", (object)DBNull.Value);
                parameters[5] = new SqlParameter("@TUGROUPID", (object)DBNull.Value);
                reader = ExecuteReader(sqlCommand, parameters);

                if (reader != null && reader.HasRows)
                {
                    reader.Read();
                    user = FillUserData();
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return user;
        }
        public List<BusinessEntity.UserManagement.User> ListUsers(BusinessEntity.UserManagement.UserFilter filter)
        {
            List<BusinessEntity.UserManagement.User> users = null;
            try
            {
                sqlCommand = "PRC_TB_USER_SE";
                SqlParameter[] parameters = new SqlParameter[6];
                parameters[0] = new SqlParameter("@TUID", filter.ID != 0 && filter.ID != Int32.MinValue ? filter.ID : (object)DBNull.Value);
                parameters[1] = new SqlParameter("@TUNAME", !String.IsNullOrEmpty(filter.Name) ? filter.Name : (object)DBNull.Value);
                parameters[2] = new SqlParameter("@TULOGIN", !String.IsNullOrEmpty(filter.Login) ? filter.Login : (object)DBNull.Value);
                parameters[3] = new SqlParameter("@TUPASSWORD", !string.IsNullOrEmpty(filter.Password) ? filter.Password : (object)DBNull.Value);
                parameters[4] = new SqlParameter("@TUPROFILERID", filter.ProfileID != 0 && filter.ProfileID != Int32.MinValue ? filter.ProfileID : (object)DBNull.Value);
                parameters[5] = new SqlParameter("@TUGROUPID", filter.GroupID != 0 && filter.GroupID != Int32.MinValue ? filter.GroupID : (object)DBNull.Value);
                reader = ExecuteReader(sqlCommand, parameters);

                if (reader != null && reader.HasRows)
                {
                    users = new List<BusinessEntity.UserManagement.User>();
                    while (reader.Read())
                        users.Add(FillUserData());
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return users;
        }
        public List<BusinessEntity.UserManagement.User> ListUsers(List<BusinessEntity.Project> selectedProjects)
        {
            List<BusinessEntity.UserManagement.User> users = null;
            try
            {
                String projects = GetProjectsXML(selectedProjects);

                sqlCommand = "PRC_TB_USER_BY_PROJECTS_SE";
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@PROJECT_LIST", !String.IsNullOrEmpty(projects.ToString()) ? projects.ToString() : (object)DBNull.Value);
                reader = ExecuteReader(sqlCommand, parameters);

                if (reader != null && reader.HasRows)
                {
                    users = new List<BusinessEntity.UserManagement.User>();
                    while (reader.Read())
                        users.Add(FillUserData());
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return users;
        }
        private BusinessEntity.UserManagement.User FillUserData()
        {
            BusinessEntity.UserManagement.User user = new BusinessEntity.UserManagement.User()
            {
                ID = MinValueInt(reader["TU_ID"]),
                Login = MinValueString(reader["TU_LOGIN"]),
                Name = MinValueString(reader["TU_NAME"]),
                Password = MinValueString(reader["TU_PASSWORD"]),
                EMailAddress = MinValueString(reader["TU_EMAIL"]),
                UserGroup = new BusinessEntity.Group()
                {
                    ID = MinValueInt(reader["TG_ID"]),
                    Name = MinValueString(reader["TG_NAME"]),
                    Identification = MinValueString(reader["TG_IDENTIFICATION"]),
                    Description = MinValueString(reader["TG_DESCRIPTION"])
                },
                UserProfile = BusinessEntity.UserManagement.Profiler.GetOne(MinValueInt(reader["TP_ID"]))
            };
            user.UserProjects = GetUserProjects(MinValueInt(reader["TU_ID"]));
            user.UserAbsences = GetUserAbsences(MinValueInt(reader["TU_ID"]));
            user.UserAdditionalDays = GetUserAdditionalDays(MinValueInt(reader["TU_ID"]));
            return user;
        }
        public List<DateTime> GetUserAdditionalDays(int userID)
        {
            List<DateTime> additionalDays = null;
            SqlDataReader readerAdditional = null;
            try
            {
                sqlCommand = "PRC_TB_USER_ADITIONAL_DAYS_SE";
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@TUADUSERID", userID);
                readerAdditional = ExecuteReader(sqlCommand, parameters);
                if (readerAdditional != null && readerAdditional.HasRows)
                {
                    additionalDays = new List<DateTime>();
                    while (readerAdditional.Read())
                        additionalDays.Add(MinValueDateTime(readerAdditional["TUAD_DATE"]));
                }
            }
            finally
            {
                if (readerAdditional != null)
                    readerAdditional.Close();
            }
            return additionalDays;
        }
        public List<BusinessEntity.UserManagement.UserAbsences> GetUserAbsences(int userID)
        {
            List<BusinessEntity.UserManagement.UserAbsences> absences = null;
            SqlDataReader readerAbsences = null;
            try
            {
                sqlCommand = "PRC_TB_USER_ABSENCES_SE";
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@TUAUSERID", userID);
                readerAbsences = ExecuteReader(sqlCommand, parameters);
                if (readerAbsences != null && readerAbsences.HasRows)
                {
                    absences = new List<BusinessEntity.UserManagement.UserAbsences>();
                    while (readerAbsences.Read())
                        absences.Add(new BusinessEntity.UserManagement.UserAbsences()
                        {
                            StartDate = MinValueDateTime(readerAbsences["TUA_START_DATE"]),
                            EndDate = MinValueDateTime(readerAbsences["TUA_END_DATE"]),
                            WeekEnd = MinValueBoolean(readerAbsences["TUA_WEEKEND"])
                        });
                }
            }
            finally
            {
                if (readerAbsences != null)
                    readerAbsences.Close();
            }
            return absences;
        }
        public List<BusinessEntity.Project> GetUserProjects(int userID)
        {
            List<BusinessEntity.Project> projects = null;
            SqlDataReader readerProject = null;

            try
            {
                sqlCommand = "PRC_TB_USER_PROJECTS_SE";
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@TUPUSERID", userID);
                readerProject = ExecuteReader(sqlCommand, parameters);
                if (readerProject != null && readerProject.HasRows)
                {
                    projects = new List<BusinessEntity.Project>();
                    while (readerProject.Read())
                        projects.Add(FillProjectData(readerProject));
                }
            }
            finally
            {
                if (readerProject != null)
                    readerProject.Close();
            }

            return projects;
        }
        private BusinessEntity.Project FillProjectData(SqlDataReader readerProject)
        {
            return new BusinessEntity.Project()
            {
                Name = MinValueString(readerProject["TUP_PROJECT"])
            };
        }
        public void SaveUser(BusinessEntity.UserManagement.User user)
        {
            try
            {
                transaction = OpenTransaction();

                sqlCommand = "PRC_TB_USER_IN";
                SqlParameter[] parameters = new SqlParameter[6];
                parameters[0] = new SqlParameter("@TU_NAME", user.Name);
                parameters[1] = new SqlParameter("@TU_LOGIN", user.Login);
                parameters[2] = new SqlParameter("@TU_PASSWORD", user.Password);
                parameters[3] = new SqlParameter("@TU_PROFILER_ID", user.UserProfile.ID);
                parameters[4] = new SqlParameter("@TU_GROUP_ID", user.UserGroup.ID);
                parameters[5] = new SqlParameter("@TU_EMAIL", user.EMailAddress);
                user.ID = ExecuteScalar(transaction, sqlCommand, parameters);

                InsertUserProjects(user, transaction);
                InsertUserAbsences(user, transaction);
                InsertUserAdditionalDays(user, transaction);

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception(ex.ToString(), ex.InnerException);
            }
        }
        public void UpdateUser(BusinessEntity.UserManagement.User user)
        {
            try
            {
                transaction = OpenTransaction();

                sqlCommand = "PRC_TB_USER_UP";
                SqlParameter[] parameters = new SqlParameter[7];
                parameters[0] = new SqlParameter("@TU_ID", user.ID);
                parameters[1] = new SqlParameter("@TU_NAME", user.Name);
                parameters[2] = new SqlParameter("@TU_LOGIN", user.Login);
                parameters[3] = new SqlParameter("@TU_PASSWORD", user.Password);
                parameters[4] = new SqlParameter("@TU_PROFILER_ID", user.UserProfile.ID);
                parameters[5] = new SqlParameter("@TU_GROUP_ID", user.UserGroup.ID);
                parameters[6] = new SqlParameter("@TU_EMAIL", user.EMailAddress);
                ExecuteNonQuery(transaction, sqlCommand, parameters);

                DeleteUserProjects(user.ID, transaction);
                DeleteUserAbsences(user.ID, transaction);
                DeleteUserAdditionalDays(user.ID, transaction);

                InsertUserProjects(user, transaction);
                InsertUserAbsences(user, transaction);
                InsertUserAdditionalDays(user, transaction);

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception(ex.ToString(), ex.InnerException);
            }
        }
        private void InsertUserAdditionalDays(BusinessEntity.UserManagement.User user, SqlTransaction transaction)
        {
            if (user.UserAdditionalDays != null && user.UserAdditionalDays.Count > 0)
            {
                foreach (DateTime itemAdditionalDay in user.UserAdditionalDays)
                {
                    sqlCommand = "PRC_TB_USER_ADITIONAL_DAYS_IN";
                    SqlParameter[] parameters = new SqlParameter[2];
                    parameters[0] = new SqlParameter("@TUAD_USER_ID", user.ID);
                    parameters[1] = new SqlParameter("@TUAD_DATE", itemAdditionalDay);
                    ExecuteNonQuery(transaction, sqlCommand, parameters);
                }
            }
        }
        private void InsertUserAbsences(BusinessEntity.UserManagement.User user, SqlTransaction transaction)
        {
            if (user.UserAbsences != null && user.UserAbsences.Count > 0)
            {
                foreach (BusinessEntity.UserManagement.UserAbsences itemAbsence in user.UserAbsences)
                {
                    sqlCommand = "PRC_TB_USER_ABSENCES_IN";
                    SqlParameter[] parameters = new SqlParameter[4];
                    parameters[0] = new SqlParameter("@TUA_USER_ID", user.ID);
                    parameters[1] = new SqlParameter("@TUA_START_DATE", itemAbsence.StartDate);
                    parameters[2] = new SqlParameter("@TUA_END_DATE", itemAbsence.EndDate);
                    parameters[3] = new SqlParameter("@TUA_WEEKEND", itemAbsence.WeekEnd);
                    ExecuteNonQuery(transaction, sqlCommand, parameters);
                }
            }
        }
        private void InsertUserProjects(BusinessEntity.UserManagement.User user, SqlTransaction transaction)
        {
            foreach (BusinessEntity.Project itemProject in user.UserProjects)
            {
                sqlCommand = "PRC_TB_USER_PROJECTS_IN";
                SqlParameter[] parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@TUP_USER_ID", user.ID);
                parameters[1] = new SqlParameter("@TUP_PROJECT", itemProject.Name);
                ExecuteNonQuery(transaction, sqlCommand, parameters);
            }
        }
        public void ChangeUserPassword(int userID, string newPassword)
        {
            sqlCommand = "PRC_TB_USER_PASSWORD_UP";
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@TU_ID", userID);
            parameters[1] = new SqlParameter("@TU_PASSWORD", newPassword);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        private void DeleteUserProjects(int userID, SqlTransaction transaction)
        {
            sqlCommand = "PRC_TB_USER_PROJECTS_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TUP_USER_ID", userID);
            ExecuteNonQuery(transaction, sqlCommand, parameters);
        }
        private void DeleteUserAbsences(int userID, SqlTransaction transaction)
        {
            sqlCommand = "PRC_TB_USER_ABSENCES_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TUA_USER_ID", userID);
            ExecuteNonQuery(transaction, sqlCommand, parameters);
        }
        private void DeleteUserAdditionalDays(int userID, SqlTransaction transaction)
        {
            sqlCommand = "PRC_TB_USER_ADITIONAL_DAYS_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TUAD_USER_ID", userID);
            ExecuteNonQuery(transaction, sqlCommand, parameters);
        }
        public void DeleteUser(int userID)
        {
            try
            {
                transaction = OpenTransaction();

                DeleteUserProjects(userID, transaction);
                DeleteUserAbsences(userID, transaction);
                DeleteUserAdditionalDays(userID, transaction);

                sqlCommand = "PRC_TB_USER_DE";
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@TU_ID", userID);
                ExecuteNonQuery(transaction, sqlCommand, parameters);

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception(ex.ToString(), ex.InnerException);
            }
        }
        public bool CheckUserFirstAccess(int userID)
        {
            bool firstAccess = false;
            try
            {
                sqlCommand = "PRC_USER_FIRST_ACCESS_SE";
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@TU_ID", userID);
                reader = ExecuteReader(sqlCommand, parameters);
                if (reader != null && reader.HasRows)
                {
                    reader.Read();
                    firstAccess = MinValueInt(reader["TU_FIRST_ACCESS"]) == 0 ? true : false;
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return firstAccess;
        }
        public void UpdateUserFirstAccess(int userID, bool state)
        {
            sqlCommand = "PRC_USER_FIRST_ACCESS_UP";
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@TU_ID", userID);
            parameters[1] = new SqlParameter("@TU_FIRST_ACCESS", state ? 1 : 0);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public List<BusinessEntity.TaskHistory> ListTaskHistory(BusinessEntity.UserManagement.User taskUser, BusinessEntity.Task task, Int32 changeRequestResultID, Int32 changeRequestMotivationID)
        {
            List<BusinessEntity.TaskHistory> history = null;
            SqlDataReader readerHistory = null;
            try
            {
                sqlCommand = "PRC_TB_USER_TASK_HISTORY_SE";
                SqlParameter[] parameters = new SqlParameter[5];
                parameters[0] = new SqlParameter("@TUH_USER_ID", taskUser != null ? taskUser.ID : (object)DBNull.Value);
                parameters[1] = new SqlParameter("@TUH_TASK_ID", task != null ? task.ID : (object)DBNull.Value);
                parameters[2] = new SqlParameter("@TUH_PROJECT", task != null ? task.Project.Name : (object)DBNull.Value);
                parameters[3] = new SqlParameter("@TUH_CHANGE_REQUEST_RESULT_ID", changeRequestResultID == 0 || changeRequestResultID == Int32.MinValue ? (object)DBNull.Value : changeRequestResultID);
                parameters[4] = new SqlParameter("@TUH_CHANGE_REQUEST_MOTIVATION_ID", changeRequestMotivationID == 0 || changeRequestMotivationID == Int32.MinValue ? (object)DBNull.Value : changeRequestMotivationID);

                readerHistory = ExecuteReader(sqlCommand, parameters);
                if (readerHistory != null && readerHistory.HasRows)
                {
                    history = new List<BusinessEntity.TaskHistory>();
                    while (readerHistory.Read())
                    {
                        BusinessEntity.TaskHistory.TaskHistoryType type = (BusinessEntity.TaskHistory.TaskHistoryType)MinValueInt(readerHistory["TUH_TYPE"]);
                        BusinessEntity.TaskHistory itemHistory = new BusinessEntity.TaskHistory()
                        {
                            ID = MinValueInt(readerHistory["TUH_ID"]),
                            StartDate = MinValueDateTime(readerHistory["TUH_START_DATE"]),
                            EndDate = MinValueDateTime(readerHistory["TUH_END_DATE"]),
                            Name = MinValueString(readerHistory["TUH_NAME"]),
                            HoursDay = MinValueInt(readerHistory["TUH_HOURS_DAY"]),
                            Description = MinValueString(readerHistory["TUH_DESCRIPTION"]),
                            HistoryType = type
                        };
                        switch (type)
                        {
                            case global::GTA.BusinessEntity.TaskHistory.TaskHistoryType.Geral:
                                itemHistory = new BusinessEntity.TaskHistoryGeneral(itemHistory);
                                ((BusinessEntity.TaskHistoryGeneral)itemHistory).Classification = new BusinessEntity.TaskDomain(MinValueInt(readerHistory["TUH_CLASSIFICATION_ID"]), MinValueString(readerHistory["TUHC_DESCRIPTION"]));
                                break;
                            case global::GTA.BusinessEntity.TaskHistory.TaskHistoryType.ChangeRequest:
                                itemHistory = new BusinessEntity.TaskHistoryChangeRequest(itemHistory);
                                ((BusinessEntity.TaskHistoryChangeRequest)itemHistory).ChangeRequestID = MinValueInt(readerHistory["TUH_CHANGE_REQUEST_ID"]);
                                ((BusinessEntity.TaskHistoryChangeRequest)itemHistory).ChangeRequestResult = new BusinessEntity.TaskDomain(MinValueInt(readerHistory["TCR_ID"]), MinValueString(readerHistory["TCR_DESCRIPTION"]));
                                ((BusinessEntity.TaskHistoryChangeRequest)itemHistory).ChangeRequestMotivationList = ListTaskHistoryChangeRequestMotivations(itemHistory.ID, Int32.MinValue);
                                break;
                        }
                        history.Add(itemHistory);
                    }
                }
            }
            finally
            {
                if (readerHistory != null)
                    readerHistory.Close();
            }
            return history;
        }
        private List<BusinessEntity.TaskDomain> ListTaskHistoryChangeRequestMotivations(int historyID, Int32 motivationID)
        {
            List<BusinessEntity.TaskDomain> motivations = null;
            SqlDataReader readerMotivation = null;
            try
            {
                sqlCommand = "PRC_TB_USER_TASK_HISTORY_CHANGE_REQUEST_MOTIVATION_SE";
                SqlParameter[] parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@TUHM_HISTORY_ID", historyID);
                parameters[1] = new SqlParameter("@TUHM_CHANGE_REQUEST_MOTIVATION_ID", motivationID != 0 && motivationID != Int32.MinValue ? motivationID : (object)DBNull.Value);
                readerMotivation = ExecuteReader(sqlCommand, parameters);

                if (readerMotivation != null && readerMotivation.HasRows)
                {
                    motivations = new List<BusinessEntity.TaskDomain>();
                    while (readerMotivation.Read())
                        motivations.Add(new BusinessEntity.TaskDomain()
                        {
                            ID = MinValueInt(readerMotivation["TCM_ID"]),
                            Description = MinValueString(readerMotivation["TCM_DESCRIPTION"])
                        });
                }
            }
            finally
            {
                if (readerMotivation != null)
                    readerMotivation.Close();
            }
            return motivations;
        }
        public List<BusinessEntity.TaskDomain> ListChangeRequestResults()
        {
            List<BusinessEntity.TaskDomain> results = null;
            try
            {
                sqlCommand = "PRC_TB_CHANGE_REQUEST_RESULTS_SE";
                reader = ExecuteReader(sqlCommand);
                if (reader != null && reader.HasRows)
                {
                    results = new List<BusinessEntity.TaskDomain>();
                    while (reader.Read())
                        results.Add(new BusinessEntity.TaskDomain()
                        {
                            ID = MinValueInt(reader["TCR_ID"]),
                            Description = MinValueString(reader["TCR_DESCRIPTION"])
                        });
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return results;
        }
        public void SaveChangeRequestResult(BusinessEntity.TaskDomain changeRequestResult)
        {
            sqlCommand = "PRC_TB_CHANGE_REQUEST_RESULTS_IN";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TCR_DESCRIPTION", changeRequestResult.Description);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void DeleteChangeRequestResult(int changeRequestResultID)
        {
            sqlCommand = "PRC_TB_CHANGE_REQUEST_RESULTS_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TCR_ID", changeRequestResultID);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void UpdateChangeRequestResult(BusinessEntity.TaskDomain changeRequestResult)
        {
            sqlCommand = "PRC_TB_CHANGE_REQUEST_RESULTS_UP";
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@TCR_ID", changeRequestResult.ID);
            parameters[1] = new SqlParameter("@TCR_DESCRIPTION", changeRequestResult.Description);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void SaveTaskHistory(BusinessEntity.Task task, BusinessEntity.UserManagement.User taskUser, BusinessEntity.TaskHistory history)
        {
            try
            {
                transaction = OpenTransaction();

                sqlCommand = "PRC_TB_USER_TASK_HISTORY_IN";
                SqlParameter[] parameters = new SqlParameter[12];
                parameters[0] = new SqlParameter("@TUH_USER_ID", taskUser.ID);
                parameters[1] = new SqlParameter("@TUH_TASK_ID", task.ID);
                parameters[2] = new SqlParameter("@TUH_PROJECT", task.Project.Name);
                parameters[3] = new SqlParameter("@TUH_NAME", history.Name);
                parameters[4] = new SqlParameter("@TUH_START_DATE", history.StartDate);
                parameters[5] = new SqlParameter("@TUH_END_DATE", history.EndDate);
                parameters[6] = new SqlParameter("@TUH_HOURS_DAY", history.HoursDay);
                parameters[7] = new SqlParameter("@TUH_DESCRIPTION", history.Description);
                parameters[8] = new SqlParameter("@TUH_TYPE", (int)history.HistoryType);
                parameters[9] = new SqlParameter("@TUH_CHANGE_REQUEST_ID", history is BusinessEntity.TaskHistoryChangeRequest ? ((BusinessEntity.TaskHistoryChangeRequest)history).ChangeRequestID : (object)DBNull.Value);
                parameters[10] = new SqlParameter("@TUH_CHANGE_REQUEST_RESULT_ID", history is BusinessEntity.TaskHistoryChangeRequest ? ((BusinessEntity.TaskHistoryChangeRequest)history).ChangeRequestResult.ID : (object)DBNull.Value);
                parameters[11] = new SqlParameter("@TUH_CLASSIFICATION_ID", history is BusinessEntity.TaskHistoryGeneral ? ((BusinessEntity.TaskHistoryGeneral)history).Classification != null ? ((BusinessEntity.TaskHistoryGeneral)history).Classification.ID : (object)DBNull.Value : (object)DBNull.Value);
                history.ID = ExecuteScalar(transaction, sqlCommand, parameters);

                if (history is BusinessEntity.TaskHistoryChangeRequest &&
                    ((BusinessEntity.TaskHistoryChangeRequest)history).ChangeRequestMotivationList != null)
                {
                    IncludeHistoryChangeRequestMotivations(history);
                }

                if (transaction != null)
                    transaction.Commit();
            }
            catch (Exception)
            {
                if (transaction != null)
                    transaction.Rollback();

                throw;
            }
        }
        private void IncludeHistoryChangeRequestMotivations(BusinessEntity.TaskHistory history)
        {
            foreach (BusinessEntity.TaskDomain itemMotivation in ((BusinessEntity.TaskHistoryChangeRequest)history).ChangeRequestMotivationList)
            {
                sqlCommand = "PRC_TB_USER_TASK_HISTORY_CHANGE_REQUEST_MOTIVATION_IN";
                SqlParameter[] parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@TUHM_HISTORY_ID", history.ID);
                parameters[1] = new SqlParameter("@TUHM_CHANGE_REQUEST_MOTIVATION_ID", itemMotivation.ID);
                ExecuteNonQuery(transaction, sqlCommand, parameters);
            }
        }
        public void UpdateTaskHistory(BusinessEntity.Task task, BusinessEntity.UserManagement.User taskUser, BusinessEntity.TaskHistory history)
        {
            try
            {
                transaction = OpenTransaction();

                sqlCommand = "PRC_TB_USER_TASK_HISTORY_UP";
                SqlParameter[] parameters = new SqlParameter[13];
                parameters[0] = new SqlParameter("@TUH_ID", history.ID);
                parameters[1] = new SqlParameter("@TUH_USER_ID", taskUser.ID);
                parameters[2] = new SqlParameter("@TUH_TASK_ID", task.ID);
                parameters[3] = new SqlParameter("@TUH_PROJECT", task.Project.Name);
                parameters[4] = new SqlParameter("@TUH_NAME", history.Name);
                parameters[5] = new SqlParameter("@TUH_START_DATE", history.StartDate);
                parameters[6] = new SqlParameter("@TUH_END_DATE", history.EndDate);
                parameters[7] = new SqlParameter("@TUH_HOURS_DAY", history.HoursDay);
                parameters[8] = new SqlParameter("@TUH_DESCRIPTION", history.Description);
                parameters[9] = new SqlParameter("@TUH_TYPE", (int)history.HistoryType);
                parameters[10] = new SqlParameter("@TUH_CHANGE_REQUEST_ID", history is BusinessEntity.TaskHistoryChangeRequest ? ((BusinessEntity.TaskHistoryChangeRequest)history).ChangeRequestID : (object)DBNull.Value);
                parameters[11] = new SqlParameter("@TUH_CHANGE_REQUEST_RESULT_ID", history is BusinessEntity.TaskHistoryChangeRequest ? ((BusinessEntity.TaskHistoryChangeRequest)history).ChangeRequestResult.ID : (object)DBNull.Value);
                parameters[12] = new SqlParameter("@TUH_CLASSIFICATION_ID", history is BusinessEntity.TaskHistoryGeneral ? ((BusinessEntity.TaskHistoryGeneral)history).Classification != null ? ((BusinessEntity.TaskHistoryGeneral)history).Classification.ID : (object)DBNull.Value : (object)DBNull.Value);
                ExecuteNonQuery(transaction, sqlCommand, parameters);

                if (history is BusinessEntity.TaskHistoryChangeRequest)
                {
                    sqlCommand = "PRC_TB_USER_TASK_HISTORY_CHANGE_REQUEST_MOTIVATION_DE";
                    parameters = new SqlParameter[2];
                    parameters[0] = new SqlParameter("@TUHM_HISTORY_ID", history.ID);
                    parameters[1] = new SqlParameter("@TUHM_CHANGE_REQUEST_MOTIVATION_ID", (object)DBNull.Value);
                    ExecuteNonQuery(transaction, sqlCommand, parameters);

                    IncludeHistoryChangeRequestMotivations(history);
                }

                if (transaction != null)
                    transaction.Commit();
            }
            catch (Exception)
            {
                if (transaction != null)
                    transaction.Rollback();
                throw;
            }

        }
        public void DeleteTaskHistory(int historyID)
        {
            sqlCommand = "PRC_TB_USER_TASK_HISTORY_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TUH_ID", historyID);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public BusinessEntity.TaskComment GetTaskComment(int taskID, int userID)
        {
            BusinessEntity.TaskComment comment = null;
            try
            {
                sqlCommand = "PRC_TB_USER_TASK_COMMENT_SE";
                SqlParameter[] parameters = new SqlParameter[2];
                parameters[0] = new SqlParameter("@TUTC_TASK_ID", taskID);
                parameters[1] = new SqlParameter("@TUTC_USER_ID", userID);
                reader = ExecuteReader(sqlCommand, parameters);
                if (reader != null && reader.HasRows)
                {
                    reader.Read();
                    comment = new BusinessEntity.TaskComment()
                    {
                        ID = MinValueInt(reader["TUTC_ID"]),
                        Comment = MinValueString(reader["TUTC_COMMENT"])
                    };
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return comment;
        }
        public void SaveTaskComment(BusinessEntity.Task task, int userID)
        {
            sqlCommand = "PRC_TB_USER_TASK_COMMENT_IN";
            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@TUTC_TASK_ID", task.ID);
            parameters[1] = new SqlParameter("@TUTC_COMMENT", task.Comment.Comment);
            parameters[2] = new SqlParameter("@TUTC_USER_ID", userID);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void UpdateTaskComment(BusinessEntity.TaskComment comment)
        {
            sqlCommand = "PRC_TB_USER_TASK_COMMENT_UP";
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@TUTC_ID", comment.ID);
            parameters[1] = new SqlParameter("@TUTC_COMMENT", comment.Comment);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public List<BusinessEntity.TaskDomain> ListChangeRequestMotivations()
        {
            List<BusinessEntity.TaskDomain> results = null;
            try
            {
                sqlCommand = "PRC_TB_CHANGE_REQUEST_MOTIVATION_SE";
                reader = ExecuteReader(sqlCommand);
                if (reader != null && reader.HasRows)
                {
                    results = new List<BusinessEntity.TaskDomain>();
                    while (reader.Read())
                        results.Add(new BusinessEntity.TaskDomain()
                        {
                            ID = MinValueInt(reader["TCM_ID"]),
                            Description = MinValueString(reader["TCM_DESCRIPTION"])
                        });
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return results;
        }
        public void SaveChangeRequestMotivation(BusinessEntity.TaskDomain changeRequestMotivation)
        {
            sqlCommand = "PRC_TB_CHANGE_REQUEST_MOTIVATION_IN";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TCM_DESCRIPTION", changeRequestMotivation.Description);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void UpdateChangeRequestMotivation(BusinessEntity.TaskDomain changeRequestResult)
        {
            sqlCommand = "PRC_TB_CHANGE_REQUEST_MOTIVATION_UP";
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@TCM_ID", changeRequestResult.ID);
            parameters[1] = new SqlParameter("@TCM_DESCRIPTION", changeRequestResult.Description);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void DeleteChangeRequestMotivation(int motivationID)
        {
            sqlCommand = "PRC_TB_CHANGE_REQUEST_MOTIVATION_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TCM_ID", motivationID);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void SaveCategory(BusinessEntity.TaskDomain category)
        {
            sqlCommand = "PRC_TB_CATEGORIES_IN";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TC_DESCRIPTION", category.Description);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void DeleteCategory(int categoryID)
        {
            sqlCommand = "PRC_TB_CATEGORIES_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TC_ID", categoryID);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void SaveState(BusinessEntity.TaskDomain state)
        {
            sqlCommand = "PRC_TB_STATES_IN";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TS_DESCRIPTION", state.Description);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void DeleteState(int stateID)
        {
            sqlCommand = "PRC_TB_STATES_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TS_ID", stateID);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public List<BusinessEntity.TaskHistoryChangeRequestReport> ListTaskHistoryChangeRequestMotivationReport(BusinessEntity.TaskHistoryFilter filter)
        {
            List<BusinessEntity.TaskHistoryChangeRequestReport> list = null;
            try
            {
                String projects = GetProjectsXML(filter.Project);
                String responsibles = GetUsersXML(filter.DesignedTo);
                sqlCommand = "PRC_REPORT_TASK_HISTORY_CHANGE_REQUEST_MOTIVATION_SE";
                SqlParameter[] parameters = new SqlParameter[6];
                parameters[0] = new SqlParameter("@projects", !String.IsNullOrEmpty(projects) ? projects : (object)DBNull.Value);
                parameters[1] = new SqlParameter("@responsibles", !String.IsNullOrEmpty(responsibles) ? responsibles : (object)DBNull.Value);
                parameters[2] = new SqlParameter("@startDateFrom", filter.StartDateFrom != DateTime.MinValue ? filter.StartDateFrom : (object)DBNull.Value);
                parameters[3] = new SqlParameter("@startDateTo", filter.StartDateTo != DateTime.MinValue ? filter.StartDateTo : (object)DBNull.Value);
                parameters[4] = new SqlParameter("@endDateFrom", filter.EndDateFrom != DateTime.MinValue ? filter.EndDateFrom : (object)DBNull.Value);
                parameters[5] = new SqlParameter("@endDateTo", filter.EndDateTo != DateTime.MinValue ? filter.EndDateTo : (object)DBNull.Value);

                reader = ExecuteReader(sqlCommand, parameters);
                if (reader != null && reader.HasRows)
                {
                    list = new List<BusinessEntity.TaskHistoryChangeRequestReport>();
                    while (reader.Read())
                    {
                        list.Add(FillTaskHistoryReportChangeRequest());
                    }
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
        }
        public List<BusinessEntity.TaskHistoryChangeRequestReport> ListTaskHistoryChangeRequestResultReport(BusinessEntity.TaskHistoryFilter filter)
        {
            List<BusinessEntity.TaskHistoryChangeRequestReport> list = null;
            try
            {
                String projects = GetProjectsXML(filter.Project);
                String responsibles = GetUsersXML(filter.DesignedTo);
                sqlCommand = "PRC_REPORT_TASK_HISTORY_CHANGE_REQUEST_RESULT_SE";
                SqlParameter[] parameters = new SqlParameter[6];
                parameters[0] = new SqlParameter("@projects", !String.IsNullOrEmpty(projects) ? projects : (object)DBNull.Value);
                parameters[1] = new SqlParameter("@responsibles", !String.IsNullOrEmpty(responsibles) ? responsibles : (object)DBNull.Value);
                parameters[2] = new SqlParameter("@startDateFrom", filter.StartDateFrom != DateTime.MinValue ? filter.StartDateFrom : (object)DBNull.Value);
                parameters[3] = new SqlParameter("@startDateTo", filter.StartDateTo != DateTime.MinValue ? filter.StartDateTo : (object)DBNull.Value);
                parameters[4] = new SqlParameter("@endDateFrom", filter.EndDateFrom != DateTime.MinValue ? filter.EndDateFrom : (object)DBNull.Value);
                parameters[5] = new SqlParameter("@endDateTo", filter.EndDateTo != DateTime.MinValue ? filter.EndDateTo : (object)DBNull.Value);

                reader = ExecuteReader(sqlCommand, parameters);
                if (reader != null && reader.HasRows)
                {
                    list = new List<BusinessEntity.TaskHistoryChangeRequestReport>();
                    while (reader.Read())
                        list.Add(FillTaskHistoryReportChangeRequest());
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
        }
        public List<BusinessEntity.TaskHistoryReport> ListTaskHistoryReport(BusinessEntity.TaskHistoryFilter filter)
        {
            List<BusinessEntity.TaskHistoryReport> list = null;
            try
            {
                String projects = GetProjectsXML(filter.Project);
                String responsibles = GetUsersXML(filter.DesignedTo);
                sqlCommand = "PRC_REPORT_TASK_HISTORY_GENERAL_SE";
                SqlParameter[] parameters = new SqlParameter[6];
                parameters[0] = new SqlParameter("@projects", !String.IsNullOrEmpty(projects) ? projects : (object)DBNull.Value);
                parameters[1] = new SqlParameter("@responsibles", !String.IsNullOrEmpty(responsibles) ? responsibles : (object)DBNull.Value);
                parameters[2] = new SqlParameter("@startDateFrom", filter.StartDateFrom != DateTime.MinValue ? filter.StartDateFrom : (object)DBNull.Value);
                parameters[3] = new SqlParameter("@startDateTo", filter.StartDateTo != DateTime.MinValue ? filter.StartDateTo : (object)DBNull.Value);
                parameters[4] = new SqlParameter("@endDateFrom", filter.EndDateFrom != DateTime.MinValue ? filter.EndDateFrom : (object)DBNull.Value);
                parameters[5] = new SqlParameter("@endDateTo", filter.EndDateTo != DateTime.MinValue ? filter.EndDateTo : (object)DBNull.Value);

                reader = ExecuteReader(sqlCommand, parameters);
                if (reader != null && reader.HasRows)
                {
                    list = new List<BusinessEntity.TaskHistoryReport>();
                    while (reader.Read())
                        list.Add(FillTaskHistoryReport());
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
        }
        private String GetProjectsXML(List<BusinessEntity.Project> projectsList)
        {
            StringBuilder projects = new StringBuilder();
            if (projectsList != null && projectsList.Count > 0)
            {
                projects.Append("<ProjectSearch>");
                projectsList.ForEach(it => projects.Append("<Projects><project_id>" + it.Name + "</project_id></Projects>"));
                projects.Append("</ProjectSearch>");
            }
            return projects.ToString();
        }
        private String GetUsersXML(List<BusinessEntity.UserManagement.User> list)
        {
            StringBuilder users = new StringBuilder();
            if (list != null && list.Count > 0)
            {
                users.Append("<UserSearch>");
                list.ForEach(it => users.Append("<Users><user_ident>" + it.ID.ToString() + "</user_ident></Users>"));
                users.Append("</UserSearch>");
            }
            return users.ToString();
        }
        private BusinessEntity.TaskHistoryChangeRequestReport FillTaskHistoryReportChangeRequest()
        {
            return new BusinessEntity.TaskHistoryChangeRequestReport()
                           {
                               ArchitectName = MinValueString(reader["tu_name"]),
                               ProjectName = MinValueString(reader["tuh_project"]),
                               ChangeRequestMotivation = MinValueString(reader["tcm_description"]),
                               ChangeRequestResult = MinValueString(reader["tcr_description"]),
                               StartDate = MinValueDateTime(reader["tuh_start_date"]),
                               EndDate = MinValueDateTime(reader["tuh_end_date"]),
                               HoursDay = MinValueInt(reader["tuh_hours_day"])
                           };
        }
        private BusinessEntity.TaskHistoryReport FillTaskHistoryReport()
        {
            return new BusinessEntity.TaskHistoryReport()
            {
                ArchitectName = MinValueString(reader["tu_name"]),
                ProjectName = MinValueString(reader["tuh_project"]),
                Classification = MinValueString(reader["tuhc_description"]),
                StartDate = MinValueDateTime(reader["tuh_start_date"]),
                EndDate = MinValueDateTime(reader["tuh_end_date"]),
                HoursDay = MinValueInt(reader["tuh_hours_day"])
            };
        }
        public void UpdateTaskData(BusinessEntity.Task task, BusinessEntity.UserManagement.User loggedUser)
        {
            String query = @"SELECT [System.Id] FROM WorkItems 
            WHERE [System.WorkItemType] = '" + loggedUser.UserGroup.Identification + @"' 
            and [System.Id] = " + task.ID.ToString() + @" ";
            query += " and [System.TeamProject] <> 'TEST' ";

            WorkItemCollection col = ExecuteQuery(query);
            if (col != null && col.Count > 0)
            {
                foreach (WorkItem item in col)
                {
                    if (item.Id.CompareTo(task.ID) == 0)
                    {
                        item.Open();

                        item.Fields["Bovespa.Sinacor.InicioRealizado"].Value = task.ScheduleRealizedStart != DateTime.MinValue ? task.ScheduleRealizedStart.ToString("dd/MM/yyyy HH:mm:ss") : null;
                        item.Fields["Bovespa.Sinacor.TerminoRealizado"].Value = task.ScheduleRealizedEnd != DateTime.MinValue ? task.ScheduleRealizedEnd.ToString("dd/MM/yyyy HH:mm:ss") : null;
                        item.Fields["Bovespa.Sinacor.HorasRealizadas"].Value = task.ScheduleRealizedHours != Int32.MinValue && task.ScheduleRealizedHours != 0 ? task.ScheduleRealizedHours.ToString() : null;
                        if (task.State != null)
                            item.State = task.State.Description;
                        item.History = task.History;

                        System.Collections.ArrayList validationResult = item.Validate();
                        if (validationResult.Count > 0)
                        {
                            ExceptionHandling.ValidationException vex = new ExceptionHandling.ValidationException("Existem campos inválidos na task.");
                            foreach (Microsoft.TeamFoundation.WorkItemTracking.Client.Field itemResult in validationResult)
                            {
                                ExceptionHandling.ValidationField validationField = new ExceptionHandling.ValidationField(itemResult.FieldDefinition.Name, itemResult.OriginalValue.ToString());
                                foreach (String itemAllowed in itemResult.AllowedValues)
                                    validationField.AddAllowedValue(itemAllowed);
                                vex.AddValidationError(validationField);
                            }
                            throw vex;
                        }

                        item.Save();
                        break;
                    }
                }
            }
        }
        public List<BusinessEntity.UserManagement.Functionality> ListProfilerFunctionalities(BusinessEntity.UserManagement.Profiler profiler)
        {
            List<BusinessEntity.UserManagement.Functionality> list = null;
            try
            {
                sqlCommand = "PRC_TB_PROFILERS_FUNCTIONALITIES_SE";
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@tp_id", profiler != null ? profiler.ID : (object)DBNull.Value);
                reader = ExecuteReader(sqlCommand, parameters);
                if (reader != null && reader.HasRows)
                {
                    list = new List<BusinessEntity.UserManagement.Functionality>();
                    while (reader.Read())
                        list.Add(new BusinessEntity.UserManagement.Functionality()
                        {
                            ID = MinValueInt(reader["tf_id"]),
                            Description = MinValueString(reader["tf_description"])
                        });
                }
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
        }
        public void SaveProfilerFunctionalities(List<BusinessEntity.UserManagement.Functionality> functinoalities, BusinessEntity.UserManagement.Profiler profiler)
        {
            try
            {
                transaction = OpenTransaction();

                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@tp_id", profiler.ID);
                sqlCommand = "PRC_TB_PROFILERS_FUNCTIONALITIES_DE";
                ExecuteNonQuery(transaction, sqlCommand, parameters);

                if (functinoalities != null && functinoalities.Count > 0)
                {
                    sqlCommand = "PRC_TB_PROFILERS_FUNCTIONALITIES_IN";
                    parameters = new SqlParameter[2];
                    foreach (BusinessEntity.UserManagement.Functionality itemFunctionality in functinoalities)
                    {
                        parameters[0] = new SqlParameter("@tpf_profiler_id", profiler.ID);
                        parameters[1] = new SqlParameter("@tpf_functionality_id", itemFunctionality.ID);
                        ExecuteNonQuery(transaction, sqlCommand, parameters);
                    }
                }

                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
        }
        public void SaveTaskHistoryGeneralClassification(BusinessEntity.TaskDomain classification)
        {
            sqlCommand = "PRC_TB_USER_TASK_HISTORY_CLASSIFICATION_IN";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TUHC_DESCRIPTION", classification.Description);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void UpdateTaskHistoryGeneralClassification(BusinessEntity.TaskDomain classification)
        {
            sqlCommand = "PRC_TB_USER_TASK_HISTORY_CLASSIFICATION_UP";
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@TUHC_ID", classification.ID);
            parameters[1] = new SqlParameter("@TUHC_DESCRIPTION", classification.Description);
            ExecuteNonQuery(sqlCommand, parameters);
        }
        public void DeleteTaskHistoryGeneralClassification(int classificationID)
        {
            sqlCommand = "PRC_TB_USER_TASK_HISTORY_CLASSIFICATION_DE";
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@TUHC_ID", classificationID);
            ExecuteNonQuery(sqlCommand, parameters);
        }
    }
}
